package com.ugoodtech.mdcc.core.repository;

import com.ugoodtech.mdcc.core.domain.StationCustomerReport;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

public interface StationCustomerReportRepository extends PagingAndSortingRepository<StationCustomerReport, Long>
        , QueryDslPredicateExecutor<StationCustomerReport> {

    @Query("select count(id) from StationCustomerReport where report_id=:report_id and answer_id=:answer_id")
    Object countRegion(@Param("report_id") Long report_id, @Param("answer_id") Long answer_id);

    @Query("from StationCustomerReport where report_id=:report_id and answer_id=:answer_id and question_id=:question_id")
    Object countScore(@Param("report_id") Long report_id, @Param("answer_id") Long answer_id, @Param("question_id") Long question_id);

    @Query(value = "select b.answer_id as answer_id,COUNT(b.id) as num from station_report a, station_customer_report b where a.id=b.report_id and a.creation_time>=:startTime and a.creation_time<=:endTime GROUP BY b.answer_id", nativeQuery = true)
    List<Object[]> countScore(@Param("startTime") Date startTime, @Param("endTime") Date endTime);

    //eg:stationCustomerReportRepository.getSatisfactionReport("2018-06-19 00:00:00", "2018-06-29 23:59:59", "1", "1", "aaaa")
    @Query(value = "call GetSatisfactionReport(:startTime, :endTime, :stationId, :userType, :userName,:serviceNum,:service_type_id)", nativeQuery = true)
    List<Object[]> getSatisfactionReport(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("stationId") String stationId, @Param("userType") String userType, @Param("userName") String userName,
                                         @Param("serviceNum") String serviceNum, @Param("service_type_id") String serviceType);

    @Query("from StationCustomerReport where report_id=:report_id")
    List<StationCustomerReport> selectAnswer(@Param("report_id") Long report_id);

    @Query("from StationCustomerReport where report_id=:report_id and question_id=:question_id and deleted=false and enabled=true")
    StationCustomerReport selectQuestion(@Param("report_id") Long report_id, @Param("question_id") Long question_id);

}
